/**
 *
 */
package user_client;

import database_objects.*;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.logging.Level;
import java.util.logging.Logger;

import oracle.jdbc.internal.ClientDataSupport;

//import database_objects.Department;
//import library.DepartmentManagerPane;
/**
 *
 * @author Pawel Rychly
 *
 */
public abstract class DataBaseClient {

    /**
     * Represents connection to database
     */
    private Connection connection;
    private String password = null;
    private String userName = null;

    public DataBaseClient(Connection connection, String userName, String password) {
        this.connection = connection;
        this.userName = userName;
        this.password = password;
    }

    //returns informations about user credibility
    private boolean checkUserCredibility() {
        //TODO: definition of this function 
        return true;
    }

    /**
     * Function finds a book in database;
     */
    public void searchBook() {
        //TODO: definition of this function
    }

    /**
     * Function returns a Connection object.
     *
     * @return
     */
    public Connection getConnection() {
        return connection;
    }

    /**
     * Returns a password of this client.
     *
     * @return password.
     */
    public String getPassword() {
        return password;
    }

    /**
     * Returns name of this client.
     *
     * @return name.
     */
    public String getUserName() {
        return userName;
    }

    private ResultSet simpleSearchValues(ArrayList<String> cols,
            ArrayList<String> values,
            String nameOfTable) throws SQLException {

        String whereCondition = "";
        for (Integer i = 0; i < cols.size(); i++) {
            if (cols.get(i).startsWith("data")) {
                if (i > 0) {
                    whereCondition += " and ";
                }
                String value = values.get(i);
                whereCondition += ("(" + cols.get(i) + " = to_date('" + value + "', 'yyyy-mm-dd' ))");
            } else {
                if (i < values.size() && (values.get(i) != null)) {
                    if (i > 0) {
                        whereCondition += " and ";
                    }
                    String value = values.get(i);
                    whereCondition += ("( lower(substr(" + cols.get(i)
                            + ", 0, " + value.length() + ")) = lower('" + value + "' ))");

                }
            }
        }
        if (whereCondition.length() > 0) {
            whereCondition = " where " + whereCondition;
        }
        System.out.println("where condition:");
        System.out.println(whereCondition);


        Statement stmt = this.connection.createStatement();
        ResultSet res = stmt.executeQuery("select * from " + nameOfTable + whereCondition);


        return res;
    }

    public Department getDepartment(Integer id) throws SQLException {

        ArrayList<String> cols = new ArrayList<String>();
        ArrayList<String> values = new ArrayList<String>();

        cols.add("id_dzialu");
        values.add(id.toString());

        ResultSet result = simpleSearchValues(cols, values, "dzialy");
        Department department = null;
        while (result.next()) {
            department = new Department(
                    result.getString("nazwa"),
                    result.getString("id_dzialu"),
                    result.getString("nr_regalu"));
        }

        result.close();


        return department;
    }

    public ArrayList<User> searchUsers(String name, String surname, String adress) throws SQLException {
        
        ArrayList<String> rows = new ArrayList<String>();
        ArrayList<String> values = new ArrayList<String>();
        
        if (name.length() > 0) {
            rows.add("imie");
            values.add(name);
        }

        if (surname.length() > 0) {
            rows.add("nazwisko");
            values.add(surname);
        }

        if (adress.length() > 0) {
            rows.add("adres");
            values.add(adress);
        }
        
        ResultSet result = simpleSearchValues(rows, values, "czytelnicy");
        ArrayList<User> res = new ArrayList<User>();
        while (result.next()) {
            res.add(new User(
                    result.getString("id_czytelnika"),
                    result.getString("imie"),
                    result.getString("nazwisko"),
                    result.getString("adres")));
        }

        result.close();

        return res;

        
    }
    
    
    public ArrayList<Book> searchBooks( String isbn,
            String nameOfEdition,
            String dateOfEdition,
            String title,
            String nameOfDepartment) throws SQLException {
        
        ArrayList<String> rows = new ArrayList<String>();

        ArrayList<String> values = new ArrayList<String>();
        if (!isbn.equals("             ")) {
            rows.add("isbn");
            values.add(isbn);
        }

        if (nameOfEdition.length() > 0) {
            rows.add("wydawnictwo");
            values.add(nameOfEdition);
        }

        if (dateOfEdition.length() > 0) {
            rows.add("data_wydania");
            values.add(dateOfEdition);
        }

        if (title.length() > 0) {
            rows.add("tytul");
            values.add(title);
        }

        if (nameOfDepartment.length() > 0) {
            rows.add("nazwa_dzialu");
            values.add(nameOfDepartment);
        }


        
        ResultSet result = simpleSearchValues(
                rows, values, "(dzialy d, wydania w where d.id_dzialu = w.id_dzialu)");
        ArrayList<String> res = new ArrayList<String>();
        while (result.next()) {
            res.add(result.getString("isbn"));
        }

        ArrayList<Book> books = new ArrayList<>();
        for (String isbnVal: res) {
            books.addAll(getBooksByEdition(isbnVal));
        }
        result.close();

        return books;

        
    }
    
    
    public ArrayList<Department> searchDepartments(
            String nameValue, String numValue) throws SQLException {
        ArrayList<String> rows = new ArrayList<String>();

        ArrayList<String> values = new ArrayList<String>();
        if (nameValue.length() > 0) {
            rows.add("nazwa");
            values.add(nameValue);
        }

        if (numValue.length() > 0) {
            rows.add("nr_regalu");
            values.add(numValue);
        }

        values.add(numValue);

        ResultSet result = simpleSearchValues(rows, values, "dzialy");
        ArrayList<Department> res = new ArrayList<Department>();
        while (result.next()) {
            res.add(new Department(
                    result.getString("nazwa"),
                    result.getString("id_dzialu"),
                    result.getString("nr_regalu")));
        }

        result.close();

        return res;

    }

    public ArrayList<Author> searchAuthors(
            String nameValue, String numValue) throws SQLException {
        ArrayList<String> rows = new ArrayList<String>();

        ArrayList<String> values = new ArrayList<String>();
        if (nameValue.length() > 0) {
            rows.add("imie");
            values.add(nameValue);
        }

        if (numValue.length() > 0) {
            rows.add("nazwisko");
            values.add(numValue);
        }

        values.add(numValue);

        ResultSet result = simpleSearchValues(rows, values, "autorzy");
        ArrayList<Author> res = new ArrayList<Author>();
        while (result.next()) {
            res.add(new Author(
                    result.getString("id_autora"),
                    result.getString("imie"),
                    result.getString("nazwisko")));
        }

        result.close();

        return res;

    }
    /*
     * public ArrayList<Book> searchBooks( String isbn, String nameOfEdition,
     * String dateOfEdition, String title, String idOfDepartment, String
     * nameValue, String numValue, String authorName, String authorSurname,
     * String positionOnboard ){
     *
     * ArrayList<String> cols = new ArrayList<String>(); ArrayList<String>
     * values = new ArrayList<String>();
     *
     * if (isbn.length() > 0) { cols.add("isbn"); values.add(isbn); }
     *
     * if (nameOfEdition.length() > 0) { cols.add("wydawnictwo");
     * values.add(nameOfEdition); }
     *
     * if (dateOfEdition.length() > 0) { cols.add("data_wydania");
     * values.add(dateOfEdition); }
     *
     * if (title.length() > 0) { cols.add("tytul"); values.add(title); }
     *
     * if (idOfDepartment.length() > 0) { cols.add("id_dzialu");
     * values.add(idOfDepartment); }
     *
     * if (nameValue.length() > 0) { cols.add("tytul"); values.add(title); }
     *
     * if (idOfDepartment.length() > 0) { cols.add("id_dzialu");
     * values.add(idOfDepartment); } }
     */

    public Author getAuthor(Integer id) throws SQLException {

        ArrayList<String> cols = new ArrayList<String>();
        ArrayList<String> values = new ArrayList<String>();

        cols.add("id_autora");
        values.add(id.toString());

        ResultSet result = simpleSearchValues(cols, values, "autorzy");
        Author author = null;
        while (result.next()) {
            author = new Author(
                    result.getString("id_autora"),
                    result.getString("imie"),
                    result.getString("nazwisko"));
        }

        result.close();


        return author;
    }

    public ArrayList<EditionBook> searchEditions(
            String isbn,
            String nameOfEdition,
            String dateOfEdition,
            String title,
            String idOfDepartment) throws SQLException {
        ArrayList<String> rows = new ArrayList<String>();

        ArrayList<String> values = new ArrayList<String>();
        if (!isbn.equals("             ")) {
            rows.add("isbn");
            values.add(isbn);
        }

        if (nameOfEdition.length() > 0) {
            rows.add("wydawnictwo");
            values.add(nameOfEdition);
        }

        if (dateOfEdition.length() > 0) {
            rows.add("data_wydania");
            values.add(dateOfEdition);
        }

        if (title.length() > 0) {
            rows.add("tytul");
            values.add(title);
        }

        if (idOfDepartment.length() > 0) {
            rows.add("id_dzialu");
            values.add(idOfDepartment);
        }

        //values.add(numValue);

        ResultSet result = simpleSearchValues(rows, values, "wydania");
        ArrayList<EditionBook> res = new ArrayList<EditionBook>();
        while (result.next()) {
            res.add(new EditionBook(result.getString("isbn"),
                    result.getString("wydawnictwo"),
                    result.getString("data_wydania"),
                    result.getString("tytul"),
                    result.getString("id_dzialu")));
        }

        result.close();

        return res;

    }

    public void removeUser(String id, String name, String nameOfTable, String col ) {
        try {
            //sprawdzenie czy nie zalega...
            removeElement(id, nameOfTable, col);
             Statement stmt = this.connection.createStatement();
            int val = stmt.executeUpdate("DROP USER " + name + "_" + id);
            connection.commit();
            stmt.close();
            
            
            
            
            
        } catch (SQLException ex) {
            Logger.getLogger(DataBaseClient.class.getName()).log(Level.SEVERE, null, ex);
        }
    
    }
    
    public void removeElement(
            String id, String nameOfTable, String col) throws SQLException {
        System.out.println("removed");
        Statement stmt = this.connection.createStatement();
        int val = stmt.executeUpdate("DELETE from "
                + nameOfTable + " where (" + col + " = " + id + ")");
        connection.commit();
        stmt.close();

    }
    
    
    

    protected boolean isExists(ArrayList<String> cols,
            ArrayList<String> values,
            String nameOfTable) throws SQLException {

        String whereCondition = "";
        for (Integer i = 0; i < cols.size(); i++) {
            if (i < values.size() && (values.get(i) != null)) {
                if (i > 0) {
                    whereCondition += " and ";
                }
                String value = values.get(i);
                whereCondition += ("( lower(" + cols.get(i)
                        + ") = lower('" + value + "' ))");

            }
        }
        if (whereCondition.length() > 0) {
            whereCondition = " where " + whereCondition;
        }
        System.out.println("where condition:");
        System.out.println(whereCondition);


        Statement stmt = this.connection.createStatement();
        ResultSet res = stmt.executeQuery("select count(*) as num from " + nameOfTable + whereCondition);
        res.next();
        Integer size = res.getInt("num");
        res.close();
        stmt.close();
        if (size > 0) {
            return true;
        } else {
            return false;
        }
    }
    
   /* 
    public ArrayList<Book> getHiredBooks() {
        ArrayList<
        return 
    }*/
    
    private String normalizeDate(String date){
        if (date!= null ){
            Integer index = date.indexOf(" ");
            if (index >= 0) {
                String substring = date.substring(0, index);
                date = substring;
            };
        }
        return date;
    }
    
    
    
    public Book setHired(Book book) throws SQLException {
        Statement stmt = this.connection.createStatement();
        ResultSet date = stmt.executeQuery("select systime as time from dual");
        String currentDate = "";
        
        while (date.next()) {
            currentDate = normalizeDate(date.getString("date"));
        }
        date.close();
        
        ResultSet res2= stmt.executeQuery(
                "select (to_date('" + currentDate + "', 'yyyy-mm-dd') - data_wypozyczenia as time from wypozyczenia"
                + "where ((id_ksiazki = " + book.getIdOfBook() + ") and (aktualny = 1 ))");

        
        
        Integer time = 0;
        while (res2.next()) {
            time = res2.getInt("time");
        }
        
        if (time > 60) {
            book.setState(Book.notavailableV);
        } else {
            book.setState(Book.notavailable);
        }
        
        return book;
    }
    
    public ArrayList<Book> getBooksByEdition(String isbn) throws SQLException {
        
        Statement stmt = this.connection.createStatement();
        
       
        
        ResultSet res = stmt.executeQuery(
                "select * from wydania w, ksiazki k where "
                + "(w.isbn = k.isbn) and ( k.isbn = '" + isbn + "')");
        ArrayList<Book> books = new ArrayList<>();
        
        
        
        while (res.next()) {
            Book book = new Book();
            
            book.setIdOfBook(res.getString("id_ksiazki"));
            book.setPositionOnBookCase(res.getString("pozycja_na_polce"));
            book.setIsbn(res.getString("isbn"));
            book.setNameOfEdition(res.getString("wydawnictwo"));
            book.setDateOfEdition(res.getString("data_wydania"));
            book.setTitle(res.getString("tytul"));
            book.setIdOfDepartment(res.getString("id_dzialu"));
            
            book = setHired(book);
            
            
            books.add(book);
        }
        stmt.close();
        return books;
    }
    
    
    
 /*   public ArrayList<Integer> findBorrowed(ArrayList<String> idsOfBooks) throws SQLException {
        Statement stmt = this.connection.createStatement();
        
        ResultSet res = stmt.executeQuery("select * from wypozyczenia where "
                + "(data_wypozyczenia is not null ) and ( k.isbn = '" + isbn + "')");
        ArrayList<Book> books = new ArrayList<>();
        
        while (res.next()) {
            Book book = new Book();
            
            book.setIdOfBook(res.getString("id_ksiazki"));
            book.setPositionOnBookCase(res.getString("pozycja_na_polce"));
            book.setIsbn(res.getString("isbn"));
            book.setNameOfEdition(res.getString("wydawnictwo"));
            book.setDateOfEdition(res.getString("data_wydania"));
            book.setTitle(res.getString("tytul"));
            book.setIdOfDepartment(res.getString("id_dzialu"));
            
            books.add(book);
        }
        stmt.close();
        return books;
        
        return 0;
    }
   */ 
    
    public HireInfo getHireInfo(String id) throws SQLException {
        ArrayList<String> cols = new ArrayList<String>();
        ArrayList<String> values = new ArrayList<String>();
      
        cols.add("id_wypozyczenia");
        values.add(id);
        ResultSet result = simpleSearchValues(cols, values, "wypozyczenia");
        EditionBook edition = null;
        
        String idOfUser = "";
        String idOfBook = "";
        String dateOfHire ="";
        String dateOfRet = "";
        Integer act = 0;
        while (result.next()) {
            idOfUser = result.getString("id_czytelnika");
            idOfBook = result.getString("id_ksiazki");
            dateOfHire = result.getString("data_wypozyczenia");
            dateOfRet = result.getString("data_zwrotu");
            act = result.getInt("aktualny");
        }
        boolean actual = false;
        if (act == 0) {
            actual = false;
        } else {
            actual = true; 
        }
        
        Book book = getBookInfo(Integer.valueOf(idOfBook));
        User user = getUserInfo(idOfUser);
        HireInfo hire= new HireInfo(id, user, book, dateOfHire, dateOfRet, actual);
        return hire;
    }
    
    
    public User getUserInfo(String id) throws SQLException {
        ArrayList<String> cols = new ArrayList<String>();
        ArrayList<String> values = new ArrayList<String>();

        cols.add("id_czytelnika");
        values.add(id);

        ResultSet result = simpleSearchValues(cols, values, "czytelnicy");

        User user = null;
        while (result.next()) {
            user = new User(
                    id, result.getString("imie"), 
                    result.getString("nazwisko"), 
                    result.getString("adres"));
        }
        
        result = simpleSearchValues(cols, values, "kary");
        while (result.next()) {
            
              user.setSum(result.getString("suma"));
        }
        
        return user;
    }
    
    
    public Book getEditionInfo(String isbn, Integer id, String position) throws SQLException {
        ArrayList<String> cols = new ArrayList<String>();
        ArrayList<String> values = new ArrayList<String>();
        
        cols.add("isbn");
        values.add(isbn);
        ResultSet result = simpleSearchValues(cols, values, "wydania");
        EditionBook edition = null;
        while (result.next()) {
            edition = new EditionBook(result.getString("isbn"),
                    result.getString("wydawnictwo"),
                    result.getString("data_wydania"),
                    result.getString("tytul"),
                    result.getString("id_dzialu"));
        }

        Department department =
                getDepartment(Integer.valueOf(edition.getIdOfDepartment()));


        cols = new ArrayList<String>();
        values = new ArrayList<String>();
        cols.add("isbn");
        values.add(isbn);
        result = simpleSearchValues(cols, values, "autorstwa");
        ArrayList<Integer> authorIds = new ArrayList<>();
        while (result.next()) {
            authorIds.add(result.getInt("id_autora"));
        }

        ArrayList<Author> authors = new ArrayList<>();
        for (Integer idAuthor : authorIds) {
            authors.add(getAuthor(idAuthor));
        }
        Book returnedBook = null;
        if ((position == null) || (id == null)) {
            returnedBook = new Book(
                    department, authors, edition);
        } else {
            returnedBook = new Book(
                    department, authors, edition, position, id.toString());

        }
        result.close();




        return returnedBook;
    }

    public Book getBookInfo(Integer id) throws SQLException {

        ArrayList<String> cols = new ArrayList<String>();
        ArrayList<String> values = new ArrayList<String>();

        cols.add("id_ksiazki");
        values.add(id.toString());

        ResultSet result = simpleSearchValues(cols, values, "ksiazki");

        String isbn = null;
        String position = null;
        while (result.next()) {
            isbn = result.getString("isbn");
            position = result.getString("pozycja_na_polce");
        }

        return getEditionInfo(isbn, id, position);
    }
}
